from collections import defaultdict

from tortoise import Tortoise
from tortoise.exceptions import DoesNotExist
from tortoise.transactions import in_transaction

from app.models.env_param_parameter import EnvParamParameter
from app.models.battery import Battery
from app.models.electric_price_info import ElectricPriceInfo
from app.models.env_param_price import EnvParamPrice
from app.models.env_parameter import EnvParameter
from app.models.parameter import Parameter
from app.schemas.env_parameter import EnvParameterCreate, EnvParameterOut, CategoryOut, \
    BatterySimple, ParameterSimple, PriceSimple, Category, PriceSimpleCategory, ParameterSimpleCategory, \
    BatterySimpleCategory, SimpleItem, CategorizedItems
from typing import List


async def create_env_parameter(data: EnvParameterCreate) -> bool:
    # 校验 battery 是否存在
    battery = await Battery.get_or_none(battery_id=data.battery)
    if not battery:
        raise ValueError("电池不存在")

    # 校验所有 price_id 是否存在（多对多）
    prices = await ElectricPriceInfo.filter(price_id__in=data.price_ids)
    if len(prices) != len(data.price_ids):
        raise ValueError("部分电价配置不存在")

    # 校验所有 parameter_id 是否存在
    parameters = await Parameter.filter(parameter_id__in=data.parameter_ids)
    if len(parameters) != len(data.parameter_ids):
        raise ValueError("部分参数 ID 不存在")

    # 一切通过，开始事务创建
    async with in_transaction() as conn:
        # 创建环境参数记录
        env_param = await EnvParameter.create(
            battery_id=data.battery,
            granularity=data.granularity,
            period=data.period,
            initial_capacity=data.initial_capacity,
            period_unit=data.period_unit,
            name=data.name,
            using_db=conn
        )

        # 插入参数关联
        param_links = [
            EnvParamParameter(env_param_id=env_param.env_param_id, parameter_id=p.parameter_id)
            for p in parameters
        ]
        await EnvParamParameter.bulk_create(param_links, using_db=conn)

        # 插入电价关联（新增表：EnvParamPrice）
        price_links = [
            EnvParamPrice(env_param_id=env_param.env_param_id, price_id=p.price_id)
            for p in prices
        ]
        await EnvParamPrice.bulk_create(price_links, using_db=conn)

    return True


async def get_env_parameter_by_id(env_param_id: int) -> dict:
    try:
        env_param = await EnvParameter.get(env_param_id=env_param_id).prefetch_related("battery")
    except DoesNotExist:
        return None

    conn = Tortoise.get_connection("default")

    # 查询价格列表（手写SQL）
    query_price = """
        SELECT p.price_id, p.price_type, p.price_name
        FROM env_param_price epp
        JOIN electric_price_info p ON epp.price_id = p.price_id
        WHERE epp.env_param_id = %s
    """
    prices = await conn.execute_query_dict(query_price, [env_param_id])

    # 查询电价值（手写SQL）
    price_value_map = {}
    for p in prices:
        price_id = p["price_id"]
        records = await conn.execute_query_dict(
            "SELECT time_point, price_amount FROM electric_price_value WHERE price_id = %s ORDER BY time_point",
            [price_id]
        )
        price_value_map[price_id] = {
            "time_list": [r["time_point"] for r in records],
            "value_list": [float(r["price_amount"]) for r in records]
        }

    # 查询参数列表（手写SQL）
    query_param = """
        SELECT param.parameter_id, param.parameter_name, param.icon,param.category_id
        FROM env_param_parameter epp
        JOIN parameter param ON epp.parameter_id = param.parameter_id
        WHERE epp.env_param_id = %s
    """
    parameters = await conn.execute_query_dict(query_param, [env_param_id])

    # 查询参数值（手写SQL）
    parameter_value_map = {}
    for p in parameters:
        parameter_id = p["parameter_id"]
        records = await conn.execute_query_dict(
            "SELECT time_point, value FROM parameter_value WHERE parameter_id = %s ORDER BY time_point",
            [parameter_id]
        )
        parameter_value_map[parameter_id] = {
            "time_list": [r["time_point"] for r in records],
            "value_list": [float(r["value"]) for r in records]
        }

    # 构造返回值
    return {
        "env_param_id": env_param.env_param_id,
        "granularity": env_param.granularity,
        "period": env_param.period,
        "period_unit": env_param.period_unit,
        "initial_capacity": env_param.initial_capacity,
        "battery": {
            "battery_id": env_param.battery.battery_id,
            "battery_name": env_param.battery.battery_name,
            "max_input_power": env_param.battery.max_input_power,
            "max_output_power": env_param.battery.max_output_power,
            "capacity": env_param.battery.capacity,
            "initial_capacity": env_param.battery.initial_capacity
        },
        "prices": [
            {
                "price_id": p["price_id"],
                "price_type": p["price_type"],
                "price_name": p["price_name"],
                **price_value_map.get(p["price_id"], {"time_list": [], "value_list": []})
            } for p in prices
        ],
        "parameters": [
            {
                "parameter_id": p["parameter_id"],
                "parameter_name": p["parameter_name"],
                "category_id": p["category_id"],
                "icon": p["icon"],
                **parameter_value_map.get(p["parameter_id"], {"time_list": [], "value_list": []})
            } for p in parameters
        ]
    }


async def list_env_parameters_grouped() -> List[EnvParameterOut]:
    # 1. 查询 env_parameter 和 battery 基础信息
    query_env = """
        SELECT ep.env_param_id, ep.granularity, ep.period, ep.period_unit,ep.initial_capacity,ep.name,
               b.battery_id, b.battery_name
        FROM env_parameter ep
        JOIN battery b ON ep.battery_id = b.battery_id
    """
    env_rows = await Tortoise.get_connection("default").execute_query_dict(query_env)

    # 2. 查询 env_param_price 和 electric_price_info 的多对多价格信息
    query_price = """
        SELECT epp.env_param_id, p.price_id, p.price_type,p.price_name
        FROM env_param_price epp
        JOIN electric_price_info p ON epp.price_id = p.price_id
    """
    price_rows = await Tortoise.get_connection("default").execute_query_dict(query_price)

    # 3. 查询 env_param_parameter 和 parameter 的参数信息
    query_param = """
        SELECT epp.env_param_id, param.parameter_id, param.parameter_name, param.icon
        FROM env_param_parameter epp
        JOIN parameter param ON epp.parameter_id = param.parameter_id
    """
    param_rows = await Tortoise.get_connection("default").execute_query_dict(query_param)

    # 分组价格信息
    price_map = defaultdict(list)
    for row in price_rows:
        price_map[row["env_param_id"]].append({
            "price_id": row["price_id"],
            "price_type": row["price_type"],
            "price_name": row["price_name"]
        })

    # 分组参数信息
    param_map = defaultdict(list)
    for row in param_rows:
        param_map[row["env_param_id"]].append({
            "parameter_id": row["parameter_id"],
            "parameter_name": row["parameter_name"],
            "icon": row["icon"]
        })

    # 合并结果
    result = []
    for row in env_rows:
        env_param_id = row["env_param_id"]
        result.append(EnvParameterOut(
            env_param_id=env_param_id,
            granularity=row["granularity"],
            period=row["period"],
            initial_capacity=row["initial_capacity"],
            period_unit=row["period_unit"],
            name=row["name"],
            battery={
                "battery_id": row["battery_id"],
                "battery_name": row["battery_name"]
            },
            prices=price_map.get(env_param_id, []),  # 改为 prices 列表
            parameters=param_map.get(env_param_id, [])
        ))

    return result


async def delete_env_parameter(env_param_id: int) -> int:
    # 删除关联的中间表记录
    await EnvParamParameter.filter(env_param_id=env_param_id).delete()

    # 先删除电价关联表记录
    await EnvParamPrice.filter(env_param_id=env_param_id).delete()

    # 再删除主表记录
    return await EnvParameter.filter(env_param_id=env_param_id).delete()



async def get_all_categories() -> CategoryOut:
    conn = Tortoise.get_connection("default")

    # 查询 battery
    query_env = """
        SELECT battery.battery_id, battery.battery_name,battery.icon,battery.background
        FROM battery
    """
    battery_rows = await conn.execute_query_dict(query_env)

    # 查询 electric_price_info
    query_price = """
        SELECT price_id,price_type,price_name,icon,background,price_type
        FROM electric_price_info
    """
    price_rows = await conn.execute_query_dict(query_price)

    # 查询 parameter
    query_param = """
        SELECT parameter_id,parameter_name,icon,background,category_id
        FROM parameter
    """
    param_rows = await conn.execute_query_dict(query_param)


    # 查询 parameter
    query_param = """
        SELECT category_id,category_name
        FROM electric_price_category
    """
    categories_price = await conn.execute_query_dict(query_param)

    # 查询 parameter
    query_param = """
        SELECT category_id,category_name
        FROM parameter_category
    """
    categories_parm = await conn.execute_query_dict(query_param)

    # 去重并转换为对象（使用 set 或 dict 去重）
    prices_dict = {}
    for row in price_rows:
        prices_dict[row["price_id"]] = PriceSimpleCategory(
            price_id=row["price_id"],
            price_type=row["price_type"],
            price_name=row["price_name"],
            icon=row["icon"],
            background=row["background"],
        )

    parameters_dict = {}
    for row in param_rows:
        parameters_dict[row["parameter_id"]] = ParameterSimpleCategory(
            parameter_id=row["parameter_id"],
            parameter_name=row["parameter_name"],
            icon=row["icon"],
            background=row["background"],
            category_id=row["category_id"]
        )

    batteries_dict = {}
    for row in battery_rows:
        batteries_dict[row["battery_id"]] = BatterySimpleCategory(
            battery_id=row["battery_id"],
            battery_name=row["battery_name"],
            icon=row["icon"],
            background=row["background"]
        )

    # 转换 electric_price_category
    categories_price = [Category(**row) for row in categories_price]

    # 转换 parameter_category
    categories_parm = [Category(**row) for row in categories_parm]

    result = CategoryOut(
        categories_price=categories_price,
        categories_parm=categories_parm,
        prices=list(prices_dict.values()),
        parameters=list(parameters_dict.values()),
        batteries=list(batteries_dict.values())
    )

    return result

async def get_all_categories_item() -> CategorizedItems:

    conn = Tortoise.get_connection("default")

    # 查询原始数据
    battery_rows = await conn.execute_query_dict("""
        SELECT battery_id, battery_name, icon, background FROM battery
    """)
    price_rows = await conn.execute_query_dict("""
        SELECT price_id, price_type, price_name, icon, background FROM electric_price_info
    """)
    param_rows = await conn.execute_query_dict("""
        SELECT parameter_id, parameter_name, icon, background, category_id FROM parameter
    """)
    categories_price = await conn.execute_query_dict("""
        SELECT category_id, category_name FROM electric_price_category
    """)
    categories_param = await conn.execute_query_dict("""
        SELECT category_id, category_name FROM parameter_category
    """)

    # 构建映射表
    category_key_to_new_id = {}
    category_id_to_obj = []
    new_category_id = 1

    def register_category(cat_type: str, old_id: int, name: str):
        nonlocal new_category_id
        key = f"{cat_type}_{old_id}"
        category_key_to_new_id[key] = new_category_id
        category_id_to_obj.append(Category(
            category_id=new_category_id,
            category_name=name
        ))
        new_category_id += 1

    # 注册价格类分类
    for row in categories_price:
        register_category("price", row["category_id"], row["category_name"])

    # 注册参数类分类
    for row in categories_param:
        register_category("parameter", row["category_id"], row["category_name"])

    # battery 是单独分类，无原始 category_id，统一加上
    register_category("battery", 0, "电池")
    battery_cat_id = category_key_to_new_id["battery_0"]

    items: List[SimpleItem] = []
    unique_id_counter = 1

    for row in battery_rows:
        items.append(SimpleItem(
            id=unique_id_counter,
            original_id=row["battery_id"],
            name=row["battery_name"],
            icon=row["icon"],
            background=row["background"],
            type="battery",
            category_id=battery_cat_id
        ))
        unique_id_counter = unique_id_counter + 1

    for row in price_rows:
        key = f"price_{row['price_type']}"
        cat_id = category_key_to_new_id.get(key)
        if cat_id:
            items.append(SimpleItem(
                id=unique_id_counter,
                original_id=row["price_id"],
                name=row["price_name"],
                icon=row["icon"],
                background=row["background"],
                type="price",
                category_id=cat_id
            ))
            unique_id_counter = unique_id_counter + 1

    for row in param_rows:
        key = f"parameter_{row['category_id']}"
        cat_id = category_key_to_new_id.get(key)
        if cat_id:
            items.append(SimpleItem(
                id=unique_id_counter,
                original_id=row["parameter_id"],
                name=row["parameter_name"],
                icon=row["icon"],
                background=row["background"],
                type="parameter",
                category_id=cat_id
            ))
            unique_id_counter = unique_id_counter + 1

    return CategorizedItems(
        categorys=category_id_to_obj,
        items=items
    )
